package polly;

import java.sql.*;
import java.util.Map;

public class JdbcRaw {
    private static Connection connection = null;

    static {
        String driver = "com.mysql.cj.jdbc.Driver";
        String dbUrl = "jdbc:mysql://localhost:3306/map?characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "123456";
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(dbUrl, user, password);
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 关闭连接,为了演示代码创建，实际未使用，但是生产环境不能忘记使用
     * @param connection
     * @param preparedStatement
     * @param resultSet
     */
    public static void closeAll(Connection connection, PreparedStatement preparedStatement,ResultSet resultSet) {
        try {
            //按顺序关闭
            if(resultSet!=null) {
                resultSet.close();
            }
            if (preparedStatement!=null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * jdbc查询
     * @param sql
     * @return
     * @throws SQLException
     */
    public static ResultSet query(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        return statement.executeQuery(sql);
    }

    /**
     * jdbc update
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int update(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        return statement.executeUpdate(sql);
    }

    /**
     * jdbc insert
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int insert(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        return statement.executeUpdate(sql);
    }

    /**
     * jdbc delete
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int delete(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        return statement.executeUpdate(sql);
    }

    /**
     * 使用事务执行sql语句
     * @param sql
     * @return
     * @throws SQLException
     */
    public static void transactionalOperateDb(String... sql) throws SQLException {
        try {
            connection.setAutoCommit(false);
            for(String sqlItem : sql){
                PreparedStatement preparedStatement = connection.prepareStatement(sqlItem);
                preparedStatement.execute();
            }
            connection.commit();
        } catch (SQLException throwables) {
            connection.rollback();
            throwables.printStackTrace();
        }
    }

    /**
     *
     */
    public static void batchUpdate(){

    }



    public static void main(String[] args) throws SQLException {
        ResultSet set = query("SELECT * FROM sign_record;");
        while (set.next()) {
            System.out.println(set.getRow() + ":" + set.getString(1));
        }
        int reflectRowsCount = update("update sign_record set signplace = '车间' where 1= 1");
        System.out.println("影响行数："+reflectRowsCount);
    }
}
